-- @owner: @songjing20
-- @date: 2023-06-07
-- @testpoint: 分区表安全测试：利用returning越权访问未授权列，合理报错

--step1:创建测试用户、分区表;expect:成功
create user u_privilege_0024 with password "Schina1234@";
create table t_privilege_0024(
partid varchar(32) not null,
col1 varchar(2) not null,
col2 date not null,
col3 varchar(8) not null
) partition by range(col2) interval('1 day')
(
partition part1 values less than ('20210331'),
partition part2 values less than ('20210401')
);
insert into t_privilege_0024 select generate_series(1, 1000), '1', '20210401', generate_series(1, 1000);
insert into t_privilege_0024 select generate_series(1, 1000), '1', '20210402', generate_series(1, 1000);
insert into t_privilege_0024 select generate_series(1, 1000), '1', '20210403', generate_series(1, 1000);
insert into t_privilege_0024 select generate_series(1, 1000), '1', '20210404', generate_series(1, 1000);
insert into t_privilege_0024 select generate_series(1, 1000), '1', '20210405', generate_series(1, 1000);
insert into t_privilege_0024 select generate_series(1, 1000), '1', '20210406', generate_series(1, 1000);
grant select (col1) on t_privilege_0024 to u_privilege_0024;
grant insert on t_privilege_0024 to u_privilege_0024;

--step2:利用insert returning 绕过分区表权限检查;expect:越权失败
set role u_privilege_0024 password "Schina1234@";
create or replace function raise_demo(id integer, name varchar(100)) returns integer language plpgsql IMMUTABLE as $$
begin
raise notice 'id :% , name : %',id,name;
return 1;
end;
$$;
/
insert into t_privilege_0024 select generate_series(1, 1000), '1', '20210406', generate_series(1, 1000) returning col2;

--step3:利用打印函数绕过分区表权限检查;expect:越权失败
insert into t_privilege_0024 select generate_series(1, 1000), '1', '20210406', generate_series(1, 1000) returning raise_demo(1, col2);

--step4:清理环境;expect:成功
reset role;
drop user u_privilege_0024 cascade;
drop table t_privilege_0024 ;
